package server;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.tomcat.util.http.fileupload.FileItem;
import org.apache.tomcat.util.http.fileupload.FileItemFactory;
import org.apache.tomcat.util.http.fileupload.disk.DiskFileItemFactory;
import org.apache.tomcat.util.http.fileupload.servlet.ServletFileUpload;

import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.util.UUID;

import json.JSONArray;
import json.JSONException;
import json.JSONObject;

/**
 * Servlet implementation class Tagin
 */
@WebServlet("/Tagin")
public class Tagin extends HttpServlet {
	private static final long serialVersionUID = 1L;
	private Connection connDB = null;
	
	/**
     * Default constructor. 
     */
    public Tagin() {
    	this.conectDB();
    }
    
    /*
     * Connect to PostGres Database
     * 
     */
    protected boolean conectDB() {
    	if(connDB!=null)
    		return true;
    	
    	try {
			Class.forName("org.postgresql.Driver");
			
			String url = "jdbc:postgresql://localhost:5432/tagin"; //tagin is the database
			Properties props = new Properties();
			props.setProperty("user","tagin");
			props.setProperty("password","tagin");
			//props.setProperty("ssl","true");
			connDB = DriverManager.getConnection(url, props);
			
			return true;
		} catch (ClassNotFoundException e) {
			System.out.println("ClassNotFound: "+e.getMessage());
		} catch (SQLException e) {
			System.out.println("Failed to connect: "+e.getMessage());
		}
		
		return false;
    }
    
    private String getDateTime() {
    	DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
    	Date date = new Date(0);
    	return dateFormat.format(date);
    }
    
	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException {
		FileWriter fstream;
		BufferedWriter outfile = null;
		
		String action = request.getParameter("action");
		Object output = null;
		
		PrintWriter out = null;
		try {
			out = response.getWriter();
		} catch (IOException e1) {
		}
		
		try {
			if(outfile==null){
				fstream = new FileWriter("/tmp/tomcatlog.txt");
			    outfile = new BufferedWriter(fstream);
			}
			
			outfile.write("\n[GET REQUEST] ["+getDateTime()+"]");
			System.out.println("\n[GET REQUEST] ["+getDateTime()+"]");
			
			Map<String, String[]> params = request.getParameterMap();
	        Set<String> keys = params.keySet();
	        Iterator<String> x = keys.iterator();
	        
	        while(x.hasNext()) {
	            String key = x.next();
	            String[] data = params.get(key);
	            
	            outfile.write(key+"=");
	            
	            for(int i=0;i<data.length;i++) {
	                outfile.write(data[i]+",");
	            }
	        }
		}catch (IOException e) {
		}
		
        if(action != null) {
			if(action.equalsIgnoreCase("list")) {
				//Called by TagListActivity ou TagManageActivity
				String type = request.getParameter("type");
				String owner = null;
				String name = null;
				int category = 0;
				int n = 0;
				int offset = 0;
				double lat = 0;
				double lon = 0;
				int radious = 0;
				
				System.out.println(request.getQueryString());
				
				String temp = request.getParameter("n");
				if(temp!=null && temp!="")
					n = Integer.parseInt(temp);
				
				temp = request.getParameter("offset");
				if(temp!=null && temp!="")
					offset = Integer.parseInt(temp);
				
				if(type.equalsIgnoreCase("all")) {
					;
				}else if(type.equalsIgnoreCase("proximity")) {
					if(request.getParameter("lat")!=null)
						lat = Double.parseDouble(request.getParameter("lat"));
					if(request.getParameter("lon")!=null)
						lon = Double.parseDouble(request.getParameter("lon"));
					if(request.getParameter("radious")!=null)
						radious = Integer.parseInt(request.getParameter("radious"));
				}else if(type.equalsIgnoreCase("category")) {
					if(request.getParameter("category")!=null)
						category = Integer.parseInt(request.getParameter("category"));
				}else if(type.equalsIgnoreCase("owned")) {
					owner = request.getParameter("owner");
				}else if(type.equalsIgnoreCase("name")) {
					try {
						name = URLDecoder.decode(request.getParameter("name"),"UTF-8");
					} catch (UnsupportedEncodingException e) {
					}
					owner = request.getParameter("owner");
				}
				
				//Request data
				output = this.listTagServlet(type, owner, name, category, n, offset, lat, lon, radious);
				
			}else if(action.equalsIgnoreCase("detail")) {
				//Called by TagDetailActivity
				int id = Integer.parseInt(request.getParameter("id"));
				
				//Request data
				output = this.detailTagServlet(id);
			}
		}else {
			JSONObject jsonObj = new JSONObject();
			try {
				jsonObj.put("status", false);
			} catch (JSONException e) {
				// TODO Auto-generated catch block
				System.out.println("JSONException: "+e.getMessage());
			}
			output = jsonObj;
		}
        
        try {
			outfile.write(output.toString());
		} catch (IOException e1) {
		}
		
		/*
         * Status codes list: en.wikipedia.org/wiki/List_of_HTTP_status_code
         */
        response.setStatus(200);
        /*
         * Just print the values
         */
        out.println(output.toString());
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException {
		PrintWriter out = null;
		try {
			out = response.getWriter();
		} catch (IOException e1) {
		}
		
		boolean isMultipart = ServletFileUpload.isMultipartContent(request);
		
		if(isMultipart) {
			// Create a factory for disk-based file items
            FileItemFactory factory = new DiskFileItemFactory();

            // Create a new file upload handler
            ServletFileUpload upload = new ServletFileUpload(factory);

            // Parse the request
            List<FileItem> items = null;
            try {
            	items = upload.parseRequest(request);
            }catch(Exception e){
            	out.print("Exception: "+e.getMessage());
            }
            
            String action = null;
            int id = 0;
            String name = null;
            String owner = null;
            int category = 0;
            String info = null;
            
            double lat = 0;
            double lon = 0;
            
            String state = null;
            
            // Process the uploaded items
            Iterator iter = items.iterator();
            while (iter.hasNext()) {
                FileItem item = (FileItem) iter.next();

                if (item.getFieldName().equals("info")) {
                    //processFormField(item);
                	//System.out.println(item.getFieldName()+": "+item.getString());
                	String[] fields = item.getString().split("&");
                	for(String field: fields) {
                		String[] nameValuePair = field.split("=");
                		System.out.println(nameValuePair[0]+"="+nameValuePair[1]);
                		if(nameValuePair[0].equals("action"))
                			action = nameValuePair[1];
                		else if(nameValuePair[0].equals("id"))
                			id = Integer.parseInt(nameValuePair[1]);
                		else if(nameValuePair[0].equals("name"))
                			name = nameValuePair[1];
                		else if(nameValuePair[0].equals("owner"))
                			owner = nameValuePair[1];
                		else if(nameValuePair[0].equals("category"))
                			category = Integer.parseInt(nameValuePair[1]);
                		else if(nameValuePair[0].equals("info"))
                			info = nameValuePair[1];
                		else if(nameValuePair[0].equals("lat"))
                			lat = Double.parseDouble(nameValuePair[1]);
                		else if(nameValuePair[0].equals("lon"))
                			lon = Double.parseDouble(nameValuePair[1]);
                		else if(nameValuePair[0].equals("state"))
                			state = nameValuePair[1];
                	}
                } else {
                    //processUploadedFile(item);
                	String imgURL;
                	File uploadedFile;
                	
                	do {
                		imgURL = owner+(UUID.randomUUID().toString().substring(0,10))+".jpg";
                		//write to file
                        uploadedFile = new File("/var/www/tagin/images/"+imgURL);
                    }while(uploadedFile.exists());
                    
                    try {
						item.write(uploadedFile);
						
						if(action.equals("add")) {
							//Request data
							response.setStatus(this.addTagServlet(name, category, info, lat, lon, owner, "http://willyou.bounceme.net/tagin/images/"+imgURL));
						}else if(action.equals("update")) {
							//Request data
							response.setStatus(this.updateTagServlet(id, name, category, info, lat, lon, owner, "http://willyou.bounceme.net/tagin/images/"+imgURL));
						}
						
	                    System.out.println("Sucess: "+response.getStatus());
					} catch (Exception e) {
						// TODO Auto-generated catch block
						response.setStatus(400);
	                    
	                    System.out.println("Failed: "+e.getMessage());
					}
                }
            }
		}else{
			String action = request.getParameter("action");
			
			System.out.println("\n[POST REQUEST] ["+getDateTime()+"]");
			
			FileWriter fstream;
			BufferedWriter outfile = null;
			
			try {
				if(outfile==null){
					fstream = new FileWriter("/tmp/tomcatlog.txt");
				    outfile = new BufferedWriter(fstream);
				}
				
				outfile.write("\n[POST REQUEST] ["+getDateTime()+"]");
			    
				Map<String, String[]> params = request.getParameterMap();
		        Set<String> keys = params.keySet();
		        Iterator<String> x = keys.iterator();
		        
		        while(x.hasNext()) {
		            String key = x.next();
		            String[] data = params.get(key);
		            
		            outfile.write(key+"=");
		            System.out.print(key+"=");
		            
		            for(int i=0;i<data.length;i++) {
		                outfile.write(data[i]+",");
		                System.out.print(data[i]+",");
		            }
		            System.out.print("&");
		        }
		        System.out.print("\n");
			} catch (IOException e1) {
			}
			
			if(action != null) {
				/*
				 * @return HTTP Status code 200 on success
				 * 							403 on fail
				 * 							500 on DB fail
				 */	
				if(action.equalsIgnoreCase("login")) {
					//Called by TagLoginActivity
					String user = request.getParameter("user");
					String pass = request.getParameter("pass");
					
					//Request data
					response.setStatus(this.loginServlet(user, pass));
				
				/*
				 * @return HTTP Status code 201 on success
				 * 							403 on fail
				 * 							500 on DB fail
				 */	
				}else if(action.equalsIgnoreCase("register")) {
					//Called by TagRegisterActivity
					String id = request.getParameter("id");
					String pass = request.getParameter("pass");
					String name = request.getParameter("name");
					String email = request.getParameter("email");
					
					//Request data
					response.setStatus(this.registerServlet(id, pass, name, email));
				
				/*
				 * @return HTTP Status code 200 on success
				 * 							403 on fail
				 * 							500 on DB fail
				 */		
				}else if(action.equalsIgnoreCase("deleteUser")) {
					//Called by TagRegisterActivity
					String id = request.getParameter("id");
					
					//Request data
					response.setStatus(this.deleteUserServlet(id));
				
				/*
				 * @return HTTP Status code 201 on success
				 * 							403 on fail
				 * 							500 on DB fail
				 */
				}else if(action.equalsIgnoreCase("add")) {
					//Called by TagAddActivity
					String name = request.getParameter("name");
					int category = Integer.parseInt(request.getParameter("category"));
					String info = request.getParameter("info");
					double lat = Double.parseDouble(request.getParameter("lat"));
					double lon = Double.parseDouble(request.getParameter("lon"));
					String owner = request.getParameter("owner");
					
					//Request data
					response.setStatus(this.addTagServlet(name, category, info, lat, lon, owner,null));
					
				/*
				 * @return HTTP Status code 200 on success
				 * 							403 on fail
				 * 							500 on DB fail
				 */	
				}else if(action.equalsIgnoreCase("update")) {
					//Called by TagManageActivity
					int id = Integer.parseInt(request.getParameter("id"));
					String name = request.getParameter("name");
					int category = Integer.parseInt(request.getParameter("category"));
					String info = request.getParameter("info");
					double lat = Double.parseDouble(request.getParameter("lat"));
					double lon = Double.parseDouble(request.getParameter("lon"));
					String owner = request.getParameter("owner");
					
					//Request data
					response.setStatus(this.updateTagServlet(id, name, category, info, lat, lon, owner, null));
					
				/*
				 * @return HTTP Status code 200 on success
				 * 							403 on fail
				 * 							500 on DB fail
				 */	
				}else if(action.equalsIgnoreCase("delete")) {
					//Called by TagManageActivity
					int id = Integer.parseInt(request.getParameter("id"));
					
					//Request data
					response.setStatus(this.deleteTagServlet(id));
					
				}else{
					response.setStatus(501);
				}
			}else {
				response.setStatus(501);
			}
			
			try {
				outfile.write("Status code: "+response.getStatus());
			    //outfile.close();
			} catch (IOException e1) {
			}
		}
		
		/*
         * Status codes list: en.wikipedia.org/wiki/List_of_HTTP_status_code
         */
        /*
         * Just print the status
         */
        out.println(response.getStatus());
	}

	/**
	 * @see HttpServlet#doPut(HttpServletRequest, HttpServletResponse)
	 */
	protected void doPut(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
	}

	/**
	 * @see HttpServlet#doDelete(HttpServletRequest, HttpServletResponse)
	 */
	protected void doDelete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
	}
	
	/*
	 * Verify user credentials Tag
	 * @params String user, String pass
	 * @return HTTP Status code 200 on success
	 * 							403 on fail
	 * 							500 on DB fail
	*/
	protected int loginServlet(String user, String pass) {
		//CONNECT TO DB
		if(this.conectDB()) {
			try {
				PreparedStatement st;
				st = this.connDB.prepareStatement("SELECT * FROM users WHERE id=? AND pass=?");
				st.setString(1, user);
				st.setString(2, pass);
				ResultSet rs = st.executeQuery();
				if(rs.next()) {
					rs.close();
					st.close();
					
					return 200;
				}
			} catch (SQLException e) {
				System.out.println("SQLException: "+e.getMessage());
			}
		}else
			return 500;
		
		return 403;
	}
	
	/*
	 * Register a new user
	 * @params String id, String pass, String name, String email
	 * @return HTTP Status code 201 on success
	 * 							403 on fail
	 * 							500 on DB fail
	 * 
	 */
	protected int registerServlet(String id, String pass, String name, String email) {
		//Connect to DB
		if(this.conectDB()) {
			try {
				PreparedStatement st;
				st = this.connDB.prepareStatement("INSERT INTO users VALUES (?,?,?,?,NULL,NULL)");
				st.setString(1, id);
				st.setString(2, pass);
				st.setString(3, name);
				st.setString(4, email);
				int rowsInserted = st.executeUpdate();
				System.out.println(rowsInserted + " rows inserted");
				
				st.close();
				
				return 201;
			} catch (SQLException e) {
				System.out.println("SQLException: "+e.getMessage());
			}
		}else
			return 500;
		
		return 403;
	}
	
	/*
	 * Delete a User
	 * @params String id
	 * @return HTTP Status code 200 on success
	 * 							403 on fail
	 * 							500 on DB fail
	 */
	protected int deleteUserServlet(String id) {
		//CONNECT TO DB
		if(this.conectDB()) {
			PreparedStatement st;
			try {
				st = this.connDB.prepareStatement("DELETE FROM users WHERE id=?");
				st.setString(1, id);
				int rowsDeleted = st.executeUpdate();
				System.out.println(rowsDeleted + " rows deleted");
				st.close();
				
				return 200;
			} catch (SQLException e) {
				System.out.println("SQLException: "+e.getMessage());
			}
		}else
			return 500;
				
		return 403;
	}
	

	/*
	 * Creates a new Tag
	 * @params String name, int category, String info, double lat, double lon, String owner
	 * @return HTTP Status code 201 on success
	 * 							403 on fail
	 * 							500 on DB fail
	 */
	protected int addTagServlet(String name, int category, String info, double lat, double lon, String owner, String imgURL) {
		//CONNECT TO DB
		if(this.conectDB()) {
			PreparedStatement st;
			try {
				st = this.connDB.prepareStatement("INSERT INTO tag VALUES (?,?,?,?,?,?,?)");
				st.setString(1, name);
				st.setString(2, owner);
				st.setInt(3, category);
				st.setString(4, info);
				st.setString(5, imgURL);
				st.setDouble(6, lat);
				st.setDouble(7, lon);
				int rowsInserted = st.executeUpdate();
				System.out.println(rowsInserted + " rows inserted");
				st.close();
				
				return 201;
			} catch (SQLException e) {
				System.out.println("SQLException: "+e.getMessage());
			}
		}else
			return 500;
		
		return 403;
	}
	
	/*
	 * Delete a Tag
	 * @params int id
	 * @return HTTP Status code 200 on success
	 * 							403 on fail
	 * 							500 on DB fail
	 */
	protected int deleteTagServlet(int id) {
		//CONNECT TO DB
		if(this.conectDB()) {
			PreparedStatement st;
			try {
				st = this.connDB.prepareStatement("DELETE FROM tag WHERE id = ?");
				st.setInt(1, id);
				int rowsDeleted = st.executeUpdate();
				System.out.println(rowsDeleted + " rows deleted");
				st.close();
				
				return 200;
			} catch (SQLException e) {
				System.out.println("SQLException: "+e.getMessage());
			}
		}else
			return 500;
		
		return 403;
	}
	
	/*
	 * Updates a Tag
	 * @params int id, String name, int category, String info, double lat, double lon, String owner
	 * @return HTTP Status code 200 on success
	 * 							403 on fail
	 * 							500 on DB fail
	 */
	protected int updateTagServlet(int id, String name, int category, String info, double lat, double lon, String owner, String imgUrl) {
		//CONNECT TO DB
		if(this.conectDB()) {
			PreparedStatement st;
			try {
				st = this.connDB.prepareStatement("UPDATE tag SET name=?, category=?, info=?, imgurl=?, lat=?, lon=?, owner=? WHERE id=?");
				st.setString(1, name);
				st.setInt(2, category);
				st.setString(3, info);
				st.setString(4, imgUrl);
				st.setDouble(5, lat);
				st.setDouble(6, lon);
				st.setString(7, owner);
				st.setInt(8, id);
				int rowsUpdated = st.executeUpdate();
				System.out.println(rowsUpdated + " rows updated");
				st.close();
				
				return 200;
			} catch (SQLException e) {
				System.out.println("SQLException: "+e.getMessage());
			}
		}else
			return 500;
		
		return 403;
	}
	
	/*
	 * Get a list of Tags
	 * 
	 */
	protected JSONArray listTagServlet(String type, String owner, String name, int category, int n, int offset, double lat, double lon, int radious) {
		JSONArray jsonArray = new JSONArray();
		
		System.out.println("LIST: "+type +", "+ owner +", "+ name +", "+ category +", "+ n +", "+ offset +", "+ lat +", "+ lon +", "+ radious);
		
		//Connect to DB
		if(this.conectDB()) {
			String query = "SELECT name, owner, category, info, imgurl, lat, lon, state, id FROM tag ";
			
			if(type.equalsIgnoreCase("category")) {
				query += "WHERE category="+category;
			}else if(type.equalsIgnoreCase("owned")) {
				query += "WHERE owner='"+owner+"'";
			}else if(type.equalsIgnoreCase("name")) {
				query += "WHERE name like '%"+name+"%' AND owner='"+owner+"'";
			}
			
			query += " ORDER BY created DESC";
			if(n>0)
				query += " LIMIT "+n;
			
			query += " OFFSET "+offset+";";
			
			try {
				PreparedStatement st;
				
				if(type.equalsIgnoreCase("proximity")) {
					String stm = "SELECT name, owner, category, info, imgurl, lat, lon, state, id FROM "+
						"tag WHERE geom && expand(transform(PointFromText('POINT("+lon+" "+lat+")',4269),32661),"+radious+") "+
						"AND distance(transform(PointFromText('POINT("+lon+" "+lat+")',4269),32661),geom) < "+radious+" ";
					
					st = this.connDB.prepareStatement(stm);
				}else{
					st = this.connDB.prepareStatement(query);
				}
				
				ResultSet rs = st.executeQuery();
				
				System.out.println("List Query: "+query);
				while (rs.next()) {
					JSONObject jsonObj = new JSONObject();
					
					jsonObj.put("name", new String(rs.getString(1)).trim());
					jsonObj.put("owner", new String(rs.getString(2)).trim());
				    jsonObj.put("category", rs.getInt(3));
				    jsonObj.put("info", new String(rs.getString(4)).trim());
				    jsonObj.put("imgurl", new String(rs.getString(5)).trim());
				    jsonObj.put("lat", rs.getDouble(6));
				    jsonObj.put("lon", rs.getDouble(7));
				    
				    if(rs.getString(8)!=null && rs.getString(8)!="")
				    	jsonObj.put("state", new String(rs.getString(8)).trim());
				    jsonObj.put("id", rs.getInt(9));
				    
				    jsonArray.put(jsonObj);
				}
				rs.close();
				st.close();
				
				return jsonArray;
			} catch (SQLException e) {
				System.out.println("[listTagServlet] SQLException: "+e.getMessage());
			} catch (JSONException e) {
				System.out.println("[listTagServlet] JSONException: "+e.getMessage());
			}
		}
		
		try {
			JSONObject jsonObj = new JSONObject();
			jsonObj.put("status", false);
			
			jsonArray.put(jsonObj);
		} catch (JSONException e) {
			System.out.println("[listTagServlet] JSONException: "+e.getMessage());
		}
		
		return jsonArray;
	}
	
	/*
	 * Get details of a Tag
	 * 
	 */
	protected JSONObject detailTagServlet(int id) {
		JSONObject jsonObj = new JSONObject();
		
		//Connect to DB
		if(this.conectDB()) {
			try {
				PreparedStatement st;
				st = this.connDB.prepareStatement("SELECT * FROM tag WHERE id=?");
				st.setInt(1, id);
				ResultSet rs = st.executeQuery();
				while (rs.next()) {
					jsonObj.put("owner", new String(rs.getString(1)).trim());
				    jsonObj.put("category", rs.getInt(2));
				    jsonObj.put("info", new String(rs.getString(3)).trim());
				    jsonObj.put("imgurl", new String(rs.getString(4)).trim());
				    jsonObj.put("lat", rs.getFloat(5));
				    jsonObj.put("lon", rs.getFloat(6));
				    jsonObj.put("state", new String(rs.getString(7)).trim());
				    jsonObj.put("id", rs.getInt(8));
				}
				rs.close();
				st.close();
				
				return jsonObj;
			} catch (SQLException e) {
				System.out.println("SQLException: "+e.getMessage());
			} catch (JSONException e) {
				System.out.println("JSONException: "+e.getMessage());
			}
		}
		
		try {
			jsonObj.put("status", false);
		} catch (JSONException e) {
			System.out.println("JSONException: "+e.getMessage());
		}
		
		return jsonObj;
	}
}
